﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interprise.Framework.CRM.Shared;

namespace InterpriseSuiteEcommerceCommon.DTO
{
    public class CustomerActivityCase
    {
        public string ActivityCode{get;set;}
    
        public string SubjectFull{ get;set; }
        public string SubjectShort{ get; set; }
        public string Status { get;set; }
        public string Priority { get;set; }
        public string AssignedTo { get;set; }
        public string ProblemText { get;set; }
        public string SolutionText { get;set; }

        public string DateCreated { get; set; }
        public string DateStarted { get; set; }

        public const string _30Days   = "30-days";
        public const string _6Months  = "6-months";
        public const string _12Months = "12-months";
        public const string _Case     = "Case";

        public static List<CustomerActivityCase> GetCustomerActivityCase(string activityStatus, string period, string searchString)
        {

            string sqlQuery = string.Empty;
            string customerCode = Customer.Current.CustomerCode;

            DateTime from = DateTime.Today;
            DateTime to = DateTime.Today.AddDays(1);

            if (period == _30Days)   from = DateTime.Today.AddDays(-30);
            if (period == _6Months)  from = DateTime.Today.AddMonths(-6);
            if (period == _12Months) from = DateTime.Today.AddMonths(-12);

            if (activityStatus == Interprise.Framework.Base.Shared.Enum.ActivityStatus.All.ToString())
            {

                if (string.IsNullOrEmpty(searchString))
                {

                    sqlQuery = string.Format("SELECT * from crmactivityView WHERE [Type] = {0} AND EntityCode = {1} AND DateCreated BETWEEN {2} AND {3} ORDER BY DateCreated DESC",
                                              DB.SQuote(_Case),
                                              DB.SQuote(customerCode), 
                                              DB.DateQuote(from.ToShortDateString()),  
                                              DB.DateQuote(to.ToShortDateString()));

                }
                else
                {

                    sqlQuery = string.Format("SELECT * from crmactivityView WHERE [Type] = {0} AND EntityCode = {1} AND DateCreated BETWEEN {2} AND {3} AND [Subject] LIKE {4} ORDER BY DateCreated DESC", 
                                             DB.SQuote(_Case),
                                             DB.SQuote(customerCode), 
                                             DB.DateQuote(from.ToShortDateString()), 
                                             DB.DateQuote(to.ToShortDateString()),
                                             DB.SQuote(string.Format("{0}{1}{0}", "%", searchString)));
                }

            }
            else
            {

                if (activityStatus == Interprise.Framework.Base.Shared.Enum.ActivityStatus.InProgress.ToString()) activityStatus = "In Progress";
                if (activityStatus == Interprise.Framework.Base.Shared.Enum.ActivityStatus.NotStarted.ToString()) activityStatus = "Not Started";

                if (string.IsNullOrEmpty(searchString))
                {

                    sqlQuery = string.Format("SELECT * from crmactivityView WHERE [Type] = {0} AND EntityCode = {1} AND [Status]={2} AND DateCreated BETWEEN {3} AND {4} ORDER BY DateCreated DESC",
                                             DB.SQuote(_Case),
                                             DB.SQuote(customerCode), 
                                             DB.SQuote(activityStatus), 
                                             DB.DateQuote(from.ToShortDateString()), 
                                             DB.DateQuote(to.ToShortDateString()));

                }
                else
                {
                    sqlQuery = string.Format("SELECT * from crmactivityView WHERE [Type] = {0} AND EntityCode = {1} AND [Status]={2} AND DateCreated BETWEEN {3} AND {4} AND [Subject] LIKE {5} ORDER BY DateCreated DESC",
                                            DB.SQuote(_Case),                                            
                                            DB.SQuote(customerCode), 
                                            DB.SQuote(activityStatus), 
                                            DB.DateQuote(from.ToShortDateString()),
                                            DB.DateQuote(to.ToShortDateString()),
                                            DB.SQuote(string.Format("{0}{1}{0}", "%", searchString)));
                }
            }

            var lstCustomerActivity = new List<CustomerActivityCase>();

            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRS(sqlQuery, con))
                {

                    string _ActivityCode = string.Empty;
                    string _Subject      = string.Empty;
                    string _Status       = string.Empty;
                    string _Priority     = string.Empty;
                    string _AssignedTo   = string.Empty;
                    string _ProblemText  = string.Empty;
                    string _SolutionText = string.Empty;
                    
                    string _DateStarted;

                    while (reader.Read())
                    {
                        //"ActivityCode" 
                       _ActivityCode = DB.RSField(reader, Const.ACTIVITYCODE_COLUMN);
                       _DateStarted  = DB.RSFieldDateTime(reader, Const.DATECREATED_COLUMN).ToShortDateString();
                       _Subject      = DB.RSField(reader, Const.SUBJECT_COLUMN);
                       _Status       = DB.RSField(reader, Const.STATUS_COLUMN);
                       _Priority     = DB.RSField(reader, Const.PRIORITY_COLUMN);
                       _AssignedTo   = DB.RSField(reader, Const.ASSIGNEDTONAME_COLUMN);
                       _ProblemText  = DB.RSField(reader, Const.PROBLEMTEXT_COLUMN);
                       _SolutionText = DB.RSField(reader, Const.SOLUTIONTEXT_COLUMN);

                       if (_Subject.Length > 75)
                       {
                           _Subject = _Subject.Substring(0, 75) + " ...";
                       }

                        lstCustomerActivity.Add(new CustomerActivityCase { ActivityCode = _ActivityCode,
                                                                           DateCreated  = _DateStarted,
                                                                           SubjectFull  = DB.RSField(reader, Const.SUBJECT_COLUMN), 
                                                                           SubjectShort = _Subject, 
                                                                           Status       = _Status, 
                                                                           Priority     = _Priority, 
                                                                           AssignedTo   = _AssignedTo,
                                                                           ProblemText  = _ProblemText,
                                                                           SolutionText = _SolutionText});

                    }

                    reader.Close();

                }
            }

            return lstCustomerActivity;
     }


  }
}
